<?php

/**
 * migrate.php
 * 2013-03-06
 * by Patrick Cho
 */

// Database Configurations

$save_log = TRUE;

$db_1 = 'live';
$db_2 = 'dat';

$db_config = array(
    'live' => array(
        'host'      => 'localhost',
        'user'      => 'root',
        'password'  => 'let2pass',
        'name'      => 'spacelive'
    ),
    'dat' => array(
        'host'      => 'localhost',
        'user'      => 'root',
        'password'  => 'let2pass',
        'name'      => 'spacedat2'
    )
);

$cutoff_uuid = 90000;
$cutoff_oid = 50000;

$show_sql = FALSE;

$files_src_url_prefix = "/var/www/files/";
$files_dest_url_prefix = "/var/www/destfiles/";

$skip_copy_files = FALSE;   // Do not copy the images to local system
$overwrite_files = TRUE;     // Download the file no matter the file is already found in the system or not

$f_mode = 0755;             // File write mode

$modify_db_1 = TRUE;

$merge_entities = array(
    //'approval',
    //'alias',
    //'banner',
    'category',                     // Overwrite
    //'cef',
    //'centre',
    //'college',
    //'enews',
    //'event',
    //'series',
    //'form',
    //'frontpage',
    //'notice',
    //'hotpick',
    //'int_subject',
    //'learning_centretype',
    //'learning_centre',
    //'media_notice',
    'menu',                         // Write new only
    'mobile_page',                  // New
    //'newsletter',
    //'news',
    'page',                         // Write new only
    //'partner',
    //'photo',
    //'programme',                  // No modification in dat
    'share',                        // Ignore old types. Overwrite new types
    //'share_related',
    //'staff',
    //'stafflist',
    'subject',                      // Overwrite all
    //'upload',
    //'video',
    //'cms_user',
    //'cms_group',
    //'log',
    //'role',
    //'menu_hotspot',
    'unit',                         // Overwrite all
    'unit_tab'                      // Overwrite all
);

/// ********************************************************************************

date_default_timezone_set('Asia/Hong_Kong');

$logging = new Logging();

if ($save_log == TRUE) {
    $logging->lfile(dirname(__FILE__) . '/migrate-log-' . @date('Ymd-His'));
}

$links = array();       // Links to database. Global Variable.

try {

    // -------------------- Setting up connection to database --------------------

    if (isset($db_config[$db_1]) == FALSE || isset($db_config[$db_2]) == FALSE) {
        throw new Exception("There are errors in database configuration.");
    }

    // Connect to database 1
    show_log("Connecting to {$db_config[$db_1]['host']}.{$db_config[$db_1]['name']} as {$db_config[$db_1]['user']} ... ");
    $links[$db_1] = mysql_connect($db_config[$db_1]['host'], $db_config[$db_1]['user'], $db_config[$db_1]['password']);
    if ($links[$db_1] == FALSE) {
        show_log("Fail\n");
        throw new Exception(mysql_error());
    }
    show_log("Success\n");

    // Connect to database 2
    show_log("Connecting to {$db_config[$db_2]['host']}.{$db_config[$db_2]['name']} as {$db_config[$db_2]['user']} ... ");
    $links[$db_2] = mysql_connect($db_config[$db_2]['host'], $db_config[$db_2]['user'], $db_config[$db_2]['password']);
    if ($links[$db_2] == FALSE) {
        show_log("Fail\n");
        throw new Exception(mysql_error());
    }
    show_log("Success\n");

    // Set utf-8 for handling Chinese characters
    mysql_query_e('SET NAMES utf8', $links[$db_1]);
    mysql_query_e('SET NAMES utf8', $links[$db_2]);

    // -------------------- Update database 1 --------------------

    show_log("Prepare to update database '{$db_config[$db_1]['name']}' ...\n");

    $error_count = 0;
    $sql_array = get_update_database_sql_array();

    if ($modify_db_1 == FALSE) {
        $sql_array = array();
    }

    use_db($db_1);

    foreach ($sql_array as $sql) {

        show_log("Execute '$sql' ... ", $show_sql);
        $flag = mysql_query($sql, $links[$db_1]);               // Run query in $sql_array

        if ($flag == FALSE) {   // Error occured
            $error_count++;
            $error_reason = mysql_error();
            show_log("Fail ($error_reason)\n", $show_sql);
        } else {
            show_log("Done\n", $show_sql);
        }
    }

    show_log("Database '{$db_config[$db_1]['name']}' has been updated ");
    if ($error_count > 0) {
        show_log("with errors. ($error_count)\n");
    } else {
        show_log("successfully.\n");
    }

    // -------------------- Merge data from database 2 to database 1 --------------------

    foreach ($merge_entities as $entity) {
        merge_entity($entity);
    }

    // -------------------- cms_global_url and global_url --------------------

    use_db($db_2);
    $result = mysql_query_e("SELECT * FROM cms_global_url", $links[$db_2]);
    use_db($db_1);
    mysql_query_e("DELETE FROM cms_global_url", $links[$db_1]);
    while ($row = mysql_fetch_assoc($result)) {
        $row = array_map('mysql_real_escape_string', $row);
        if ($modify_db_1) {
            mysql_query_e("INSERT INTO cms_global_url VALUES('" . implode("','", $row). "')", $links[$db_1]);
        }
    }

    use_db($db_2);
    $result = mysql_query_e("SELECT * FROM global_url", $links[$db_2]);
    use_db($db_1);
    mysql_query_e("DELETE FROM global_url", $links[$db_1]);
    while ($row = mysql_fetch_assoc($result)) {
        $row = array_map('mysql_real_escape_string', $row);
        if ($modify_db_1) {
            mysql_query_e("INSERT INTO global_url VALUES('" . implode("','", $row). "')", $links[$db_1]);
        }
    }

    show_log("\n***** Done *****\n");

} catch (Exception $e) {
    show_log("\nException (Line : " . $e->getLine() . "): " . $e->getMessage() . "\n");
}

// Close database connections
foreach ($links as $l) {
    mysql_close($l);
}

$logging->lclose();

show_log("\nExit\n");

exit();

// ----- Program End -----

// ********************************************************************************

// Helper Functions

function show_log($string, $show = TRUE)
{
    global $save_log, $logging;
    if ($show == TRUE) {
        echo($string);
        @ob_flush();
        @flush();
        if ($save_log == TRUE) {
            $logging->lwrite($string);
        }
    }
}

function mkdir_recursive($pathname, $mode)
{
    is_dir(dirname($pathname)) || mkdir_recursive(dirname($pathname), $mode);
    return is_dir($pathname) || @mkdir($pathname, $mode);
}

function mysql_query_e($query, $link_identifier = NULL)
{
    global $show_sql;
    if ($link_identifier == NULL) {
        throw new Exception("Link identifier is not set.");
    }
    if ($show_sql) {
        show_log("Execute '$query'");
    }
    $flag = mysql_query($query, $link_identifier);
    if ($flag == FALSE) {
        throw new Exception(mysql_error() . " (SQL : '$query')");
    }
    return $flag;
}

function use_db($db_id)
{
    global $db_config, $links;
    if (isset($db_config[$db_id]) == FALSE || isset($links[$db_id]) == FALSE) {
        throw new Exception("Database ID '$db_id' is not found.");
    }
    mysql_query_e("USE {$db_config[$db_id]['name']}", $links[$db_id]);
}

// Merge entity from $db_2 to $db_1
function merge_entity($entity)
{
    global  $db_1,
            $db_2,
            $links,
            $db_config,
            $cutoff_uuid,
            $cutoff_oid,
            $files_src_url_prefix,
            $files_dest_url_prefix,
            $skip_copy_files,
            $overwrite_files,
            $f_mode,
            $modify_db_1;

    $link_1 = $links[$db_1];
    $link_2 = $links[$db_2];
    $db_name_1 = $db_config[$db_1]['name'];
    $db_name_2 = $db_config[$db_2]['name'];

    // Retrieve all unit data from database 2
    show_log("\n***** Merge entity $entity *****\n\n");

    use_db($db_2);
    $result = mysql_query_e("SELECT * , UNIX_TIMESTAMP(publish) AS publish_ts, UNIX_TIMESTAMP(last_modified) AS last_modified_ts FROM $entity", $link_2);

    $count = 0;

    // For each row fetched from database 2
    while ($row = mysql_fetch_assoc($result)) {

        $uuid           = mysql_real_escape_string($row['uuid']);
        $oid            = mysql_real_escape_string($row['oid']);
        $publish        = mysql_real_escape_string($row['publish']);
        $last_modified  = mysql_real_escape_string($row['last_modified']);

        // ----- Determine whether to ignore the row or not -----

        // Published item only (Database 2)
        if (! (!empty($row['publish'])
            && empty($row['unpublish'])
            && $row['enabled'] == 'Y'
            && $row['saved'] == 'Y'
            && $row['deleted'] == 'N'))
        {
            //show_log("Skipped (Deleted / Not Published)\n");
            continue;
        }

        show_log("Entity $entity (UUID: $uuid, OID: $oid) ... ");

        $count++;

        // ----- Skip Check -----

        $skip = FALSE;
        $reason = array();
        $timestamp = '';

        // Check if the item with same OID exists database 1
        // Skip the item if it has a newer version in database 1
        use_db($db_1);
        $temp_result = mysql_query_e("SELECT *, UNIX_TIMESTAMP(publish) AS publish_ts, UNIX_TIMESTAMP(last_modified) AS last_modified_ts FROM {$entity} WHERE oid='$oid' AND publish IS NOT NULL AND unpublish IS NULL AND enabled='Y' AND saved='Y' AND deleted='N'", $link_1);
        $temp_row = NULL;
        if (mysql_num_rows($temp_result) > 0) {
            $temp_row = mysql_fetch_assoc($temp_result);
            $publish_ts_1 = $temp_row['publish_ts'];
            $publish_ts_2 = $row['publish_ts'];
            $timestamp = "$db_name_1: {$temp_row['publish']}, $db_name_2: {$row['publish']}";
            if ($publish_ts_1 > $publish_ts_2) {
                $reason[] = "Newer version exists in $db_name_1";
                $skip = TRUE;
            }
        }

        // Cutoff UUID
        if ($row['uuid'] < $cutoff_uuid) {
            $reason[] = "UUID is smaller than $cutoff_uuid";
            $skip = TRUE;
        }

        // Cutoff OID
        if ($row['oid'] < $cutoff_oid) {
            $reason[] = "OID is smaller than $cutoff_oid";
            $skip = TRUE;
        }

        show_log("[[[");
        if ($entity == 'menu' || $entity == 'category' || $entity == 'subject' || $entity == 'unit' || $entity == 'unit_tab') {
            show_log("Force Overwrite");
            $skip = FALSE;
        } else if ($entity == 'share') {
            use_db($db_2);
            $share_data = mysql_query_e("SELECT sharing_type FROM share_data WHERE uuid='$uuid'", $link_2);
            if (mysql_num_rows($share_data) != 1) {
                throw new Exception("Entity $entity data is not found. (UUID: $uuid, $OID: $oid)");
            }
            $share_row = mysql_fetch_assoc($share_data);
            $share_type = $share_row['sharing_type'];
            if ($share_type == 'students' || $share_type == 'tutors' || $share_type == 'organisation' || $share_type == 'article') {
                show_log("Force Skipped ($share_type)");
                $skip = TRUE;
            } else {
                show_log("Force Overwrite ($share_type)");
                $skip = FALSE;
            }
        } else if ($skip == TRUE) {
            show_log("Skipped");
        } else {
            show_log("Overwrite");
        }
        show_log("]]]");

        if (count($reason) > 0) {
            show_log(" (" . implode(', ', $reason) . ")");
        }

        show_log("\n");

        if (! empty($timestamp)) {
            show_log("*** $timestamp\n");
        }

        if ($skip == TRUE) {        // Skip the current item
            show_log("\n");
            continue;
        }

        // ----- End Skip Check -----

        use_db($db_2);
        $result_data = mysql_query_e("SELECT * FROM {$entity}_data WHERE uuid='$uuid'", $link_2);
        $result_varchar = mysql_query_e("SELECT * FROM {$entity}_varchar WHERE uuid='$uuid'", $link_2);
        $result_file = mysql_query_e("SELECT * FROM {$entity}_file WHERE uuid='$uuid'", $link_2);
        $result_number = mysql_query_e("SELECT * FROM {$entity}_number WHERE uuid='$uuid'", $link_2);
        $result_datetime = mysql_query_e("SELECT * FROM {$entity}_datetime WHERE uuid='$uuid'", $link_2);
        $result_text = mysql_query_e("SELECT * FROM {$entity}_text WHERE uuid='$uuid'", $link_2);

        if (mysql_num_rows($result_data) != 1) {
            throw new Exception("Entity $entity data is not found. (UUID: $uuid, $OID: $oid)");
        }

        $row_data = mysql_fetch_row($result_data);
        $row_data = array_map('mysql_real_escape_string', $row_data);

        use_db($db_1);

        // Unpublish the item in database 1 if any
        $temp_result = mysql_query_e("SELECT * FROM {$entity} WHERE oid='$oid' AND publish IS NOT NULL AND unpublish IS NULL AND enabled='Y' AND saved='Y' AND deleted='N'", $link_1);
        while ($temp_row = mysql_fetch_assoc($temp_result)) {
            $now = date('Y-m-d h:i:s');
            show_log("--- Unpublish $entity in $db_name_1 with UUID {$temp_row['uuid']}\n");
            if ($modify_db_1 == TRUE) {
                mysql_query_e("UPDATE {$entity} SET unpublish='$now' WHERE uuid='{$temp_row['uuid']}'", $link_1);
            }
        }

        // Delete all entity data in database 1 with this UUID
        show_log("--- Remove all $entity data from $db_name_1 with UUID $uuid\n");
        if ($modify_db_1 == TRUE) {
            mysql_query_e("DELETE FROM {$entity} WHERE uuid='$uuid'", $link_1);
            mysql_query_e("DELETE FROM {$entity}_data WHERE uuid='$uuid'", $link_1);
            mysql_query_e("DELETE FROM {$entity}_datetime WHERE uuid='$uuid'", $link_1);
            mysql_query_e("DELETE FROM {$entity}_file WHERE uuid='$uuid'", $link_1);
            mysql_query_e("DELETE FROM {$entity}_number WHERE uuid='$uuid'", $link_1);
            mysql_query_e("DELETE FROM {$entity}_text WHERE uuid='$uuid'", $link_1);
            mysql_query_e("DELETE FROM {$entity}_varchar WHERE uuid='$uuid'", $link_1);
        }

        show_log("--- Insert $entity data to $db_name_1. (UUID: $uuid, OID: $oid)\n");

        // Insert data to database 1
        if ($modify_db_1 == TRUE) {
            mysql_query_e("INSERT INTO {$entity} VALUE ('$uuid', '$oid', '$publish', NULL, 'Y', 'Y', 'N', '$last_modified')", $link_1);
            mysql_query_e("INSERT INTO {$entity}_data VALUES('" . implode("','", $row_data). "')", $link_1);
        }


        while ($row_varchar = mysql_fetch_assoc($result_varchar)) {

            $row_varchar = array_map('mysql_real_escape_string', $row_varchar);

            use_db($db_1);
            if ($modify_db_1 == TRUE) {
                mysql_query_e("INSERT INTO {$entity}_varchar VALUES ('$uuid','{$row_varchar['name']}','{$row_varchar['value']}','{$row_varchar['seq']}')", $link_1);
            }
        }

        while ($row_number = mysql_fetch_assoc($result_number)) {

            $row_number = array_map('mysql_real_escape_string', $row_number);

            use_db($db_1);
            if ($modify_db_1 == TRUE) {
                mysql_query_e("INSERT INTO {$entity}_number VALUES ('$uuid','{$row_number['name']}','{$row_number['value']}','{$row_number['seq']}')", $link_1);
            }
        }

        while ($row_datetime = mysql_fetch_assoc($result_datetime)) {

            $row_datetime = array_map('mysql_real_escape_string', $row_datetime);

            use_db($db_1);
            if ($modify_db_1 == TRUE) {
                mysql_query_e("INSERT INTO {$entity}_datetime VALUES ('$uuid','{$row_datetime['name']}','{$row_datetime['value']}','{$row_datetime['seq']}')", $link_1);
            }
        }

        while ($row_text = mysql_fetch_assoc($result_text)) {

            $row_text = array_map('mysql_real_escape_string', $row_text);

            use_db($db_1);
            if ($modify_db_1 == TRUE) {
                mysql_query_e("INSERT INTO {$entity}_text VALUES ('$uuid','{$row_text['name']}','{$row_text['value']}','{$row_text['seq']}')", $link_1);
            }
        }

        while ($row_file = mysql_fetch_assoc($result_file)) {

            $row_file = array_map('mysql_real_escape_string', $row_file);

            use_db($db_1);
            if ($modify_db_1 == TRUE) {
                mysql_query_e("INSERT INTO {$entity}_file VALUES ('$uuid','{$row_file['filename']}','{$row_file['mime']}','{$row_file['filesize']}','{$row_file['uri']}','{$row_file['alt']}')", $link_1);
            }

            // Copy files
            $files_src_url = $files_src_url_prefix . "$entity/$oid/$uuid/{$row_file['filename']}";
            $files_dest_url = $files_dest_url_prefix . "$entity/$oid/$uuid/{$row_file['filename']}";
            $dir_dest_url = $files_dest_url_prefix . "$entity/$oid/$uuid";

            if (! mkdir_recursive("$dir_dest_url", $f_mode)) {
                throw new Exception("Cannot create directory at '$dir_dest_url'");
            }

            // Skiping files copy
            if ($skip_copy_files) {
                continue;
            }

            show_log("--- Linking file from '$files_src_url' to '$files_dest_url' ... ");

            // Skip if file exists already
            if ($overwrite_files == false && file_exists("$files_dest_url")) {
                show_log("--- Already exists. Skipped.\n");
                continue;
            }

            @unlink($files_dest_url);
            $flag = @link($files_src_url, $files_dest_url);     // Setup hard link to file

            if ($flag) {
                show_log("Done\n");
            } else {
                show_log("Fail\n");
            }
        }

        show_log("\n");
    }

    show_log("Number of $entity items: $count\n");
}

function get_update_database_sql_array()
{
    $sql_array = array();

    // ********* Database Comparasion *********

    // * ListRPSUser
    //    * spacedat (localhost) is missing table: ListRPSUser.
    // * _tmp
    //    * spacedat (localhost) is missing table: _tmp.
    // * alias_data
    //    * spacelive (localhost) is missing field: publish_date
    //    * spacelive (localhost) is missing field: expiry_date

    $sql_array[] = "ALTER TABLE `alias_data` ADD `publish_date` datetime NULL AFTER `alias_url`";
    $sql_array[] = "ALTER TABLE `alias_data` ADD `expiry_date` datetime NULL AFTER `publish_date`";

    // * banner_data
    //    * spacelive (localhost) is missing field: display_options

    $sql_array[] = "ALTER TABLE `banner_data` ADD `display_options` varchar(255) COLLATE 'utf8_general_ci' NULL DEFAULT 'both' AFTER `show_alt`";

    // * category_data
    //    * spacelive (localhost) is missing field: file
    //    * spacelive (localhost) is missing field: file_cht
    //    * spacelive (localhost) is missing field: alt
    //    * spacelive (localhost) is missing field: alt_cht
    //    * spacelive (localhost) is missing field: image_link
    //    * spacelive (localhost) is missing field: image_link_cht

    $sql_array[] = "ALTER TABLE `category_data` ADD `file` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `caption_cht`";
    $sql_array[] = "ALTER TABLE `category_data` ADD `file_cht` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `file`";
    $sql_array[] = "ALTER TABLE `category_data` ADD `alt` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `file_cht`";
    $sql_array[] = "ALTER TABLE `category_data` ADD `alt_cht` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `alt`";
    $sql_array[] = "ALTER TABLE `category_data` ADD `image_link` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `alt_cht`";
    $sql_array[] = "ALTER TABLE `category_data` ADD `image_link_cht` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `image_link`";

    // * cms_log
    //    * Field entity differs between databases for parameter 'Key'. spacelive (localhost) has '' and spacedat (localhost) has 'MUL'.
    //    * Field oid differs between databases for parameter 'Key'. spacelive (localhost) has '' and spacedat (localhost) has 'MUL'.
    //    * Field uuid differs between databases for parameter 'Key'. spacelive (localhost) has '' and spacedat (localhost) has 'MUL'.
    //    * Field user_action differs between databases for parameter 'Key'. spacelive (localhost) has '' and spacedat (localhost) has 'MUL'.

/*
    $sql_array[] = "ALTER TABLE `cms_log` ADD INDEX `user_action` (`user_action`)";
    $sql_array[] = "ALTER TABLE `cms_log` ADD INDEX `entity` (`entity`)";
    $sql_array[] = "ALTER TABLE `cms_log` ADD INDEX `oid` (`oid`)";
    $sql_array[] = "ALTER TABLE `cms_log` ADD INDEX `uuid` (`uuid`)";
*/

    // * em_user_status_change_log
    //    * spacedat (localhost) is missing table: em_user_status_change_log.
    // * em_user_status_change_requests
    //    * spacedat (localhost) is missing table: em_user_status_change_requests.
    // * event_data
    //    * spacelive (localhost) is missing field: unit

    $sql_array[] = "ALTER TABLE `event_data` ADD `unit` text COLLATE 'utf8_general_ci' NULL AFTER `url`";

    // * logistics
    //    * spacedat (localhost) is missing table: logistics.
    // * photo_data
    //    * spacelive (localhost) is missing field: cate
    //    * spacelive (localhost) is missing field: cate_cht

    $sql_array[] = "ALTER TABLE `photo_data` ADD `cate` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `name_cht`";
    $sql_array[] = "ALTER TABLE `photo_data` ADD `cate_cht` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `cate`";

    // * programme_bak_20100313
    //    * spacedat (localhost) is missing table: programme_bak_20100313.
    // * programme_data
    //    * spacelive (localhost) is missing field: application_caption
    //    * spacelive (localhost) is missing field: date_time_json

    $sql_array[] = "ALTER TABLE `programme_data` ADD `application_caption` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `teacher_tbc`";
    $sql_array[] = "ALTER TABLE `programme_data` ADD `date_time_json` varchar(255) COLLATE 'utf8_general_ci' NULL";

    // * registration_backup_for_openday_2010
    //    * spacedat (localhost) is missing table: registration_backup_for_openday_2010.
    // * registration_label
    //    * Field value differs between databases for parameter 'Type'. spacelive (localhost) has 'varchar(255)' and spacedat (localhost) has 'varchar(1000)'.

    $sql_array[] = "ALTER TABLE `registration_label` CHANGE `value` `value` varchar(1000) COLLATE 'utf8_general_ci' NOT NULL AFTER `label`";

    // * share_data
    //    * spacelive (localhost) is missing field: video_cht
    //    * spacelive (localhost) is missing field: video_mp4
    //    * spacelive (localhost) is missing field: video_mp4_cht
    //    * spacelive (localhost) is missing field: url

    $sql_array[] = "ALTER TABLE `share_data` ADD `video_cht` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `video`";
    $sql_array[] = "ALTER TABLE `share_data` ADD `video_mp4` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `video_cht`";
    $sql_array[] = "ALTER TABLE `share_data` ADD `video_mp4_cht` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `video_mp4`";
    $sql_array[] = "ALTER TABLE `share_data` ADD `url` varchar(255) COLLATE 'utf8_general_ci' NULL";

    // * subject_data
    //    * spacelive (localhost) is missing field: unit
    //    * spacelive (localhost) is missing field: description
    //    * spacelive (localhost) is missing field: description_cht
    //    * spacelive (localhost) is missing field: right_column
    //    * spacelive (localhost) is missing field: right_column_cht

    $sql_array[] = "ALTER TABLE `subject_data` ADD `unit` varchar(255) COLLATE 'utf8_general_ci' NULL AFTER `priority`";
    $sql_array[] = "ALTER TABLE `subject_data` ADD `description` longtext COLLATE 'utf8_general_ci' NULL AFTER `unit`";
    $sql_array[] = "ALTER TABLE `subject_data` ADD `description_cht` longtext COLLATE 'utf8_general_ci' NULL AFTER `description`";
    $sql_array[] = "ALTER TABLE `subject_data` ADD `right_column` longtext COLLATE 'utf8_general_ci' NULL AFTER `description_cht`";
    $sql_array[] = "ALTER TABLE `subject_data` ADD `right_column_cht` longtext COLLATE 'utf8_general_ci' NULL AFTER `right_column`";

    // * swong
    //    * spacedat (localhost) is missing table: swong.
    // * temp_user
    //    * spacedat (localhost) is missing table: temp_user.
    // * approval_notice
    //    * spacelive (localhost) is missing table: approval_notice.
    // * menu_hotspot
    //    * spacelive (localhost) is missing table: menu_hotspot.
    // * menu_hotspot_data
    //    * spacelive (localhost) is missing table: menu_hotspot_data.
    // * menu_hotspot_datetime
    //    * spacelive (localhost) is missing table: menu_hotspot_datetime.
    // * menu_hotspot_file
    //    * spacelive (localhost) is missing table: menu_hotspot_file.
    // * menu_hotspot_number
    //    * spacelive (localhost) is missing table: menu_hotspot_number.
    // * menu_hotspot_text
    //    * spacelive (localhost) is missing table: menu_hotspot_text.
    // * menu_hotspot_varchar
    //    * spacelive (localhost) is missing table: menu_hotspot_varchar.
    // * mobile_page
    //    * spacelive (localhost) is missing table: mobile_page.
    // * mobile_page_data
    //    * spacelive (localhost) is missing table: mobile_page_data.
    // * mobile_page_datetime
    //    * spacelive (localhost) is missing table: mobile_page_datetime.
    // * mobile_page_file
    //    * spacelive (localhost) is missing table: mobile_page_file.
    // * mobile_page_number
    //    * spacelive (localhost) is missing table: mobile_page_number.
    // * mobile_page_text
    //    * spacelive (localhost) is missing table: mobile_page_text.
    // * mobile_page_varchar
    //    * spacelive (localhost) is missing table: mobile_page_varchar.
    // * notice
    //    * spacelive (localhost) is missing table: notice.
    // * notice_data
    //    * spacelive (localhost) is missing table: notice_data.
    // * notice_datetime
    //    * spacelive (localhost) is missing table: notice_datetime.
    // * notice_file
    //    * spacelive (localhost) is missing table: notice_file.
    // * notice_number
    //    * spacelive (localhost) is missing table: notice_number.
    // * notice_text
    //    * spacelive (localhost) is missing table: notice_text.
    // * notice_varchar
    //    * spacelive (localhost) is missing table: notice_varchar.
    // * share_related
    //    * spacelive (localhost) is missing table: share_related.
    // * share_related_data
    //    * spacelive (localhost) is missing table: share_related_data.
    // * share_related_datetime
    //    * spacelive (localhost) is missing table: share_related_datetime.
    // * share_related_file
    //    * spacelive (localhost) is missing table: share_related_file.
    // * share_related_number
    //    * spacelive (localhost) is missing table: share_related_number.
    // * share_related_text
    //    * spacelive (localhost) is missing table: share_related_text.
    // * share_related_varchar
    //    * spacelive (localhost) is missing table: share_related_varchar.
    // * sharing
    //    * spacelive (localhost) is missing table: sharing.
    // * sharing_data
    //    * spacelive (localhost) is missing table: sharing_data.
    // * sharing_datetime
    //    * spacelive (localhost) is missing table: sharing_datetime.
    // * sharing_file
    //    * spacelive (localhost) is missing table: sharing_file.
    // * sharing_number
    //    * spacelive (localhost) is missing table: sharing_number.
    // * sharing_text
    //    * spacelive (localhost) is missing table: sharing_text.
    // * sharing_varchar
    //    * spacelive (localhost) is missing table: sharing_varchar.
    // * unit
    //    * spacelive (localhost) is missing table: unit.
    // * unit_data
    //    * spacelive (localhost) is missing table: unit_data.
    // * unit_datetime
    //    * spacelive (localhost) is missing table: unit_datetime.
    // * unit_file
    //    * spacelive (localhost) is missing table: unit_file.
    // * unit_number
    //    * spacelive (localhost) is missing table: unit_number.
    // * unit_tab
    //    * spacelive (localhost) is missing table: unit_tab.
    // * unit_tab_data
    //    * spacelive (localhost) is missing table: unit_tab_data.
    // * unit_tab_datetime
    //    * spacelive (localhost) is missing table: unit_tab_datetime.
    // * unit_tab_file
    //    * spacelive (localhost) is missing table: unit_tab_file.
    // * unit_tab_number
    //    * spacelive (localhost) is missing table: unit_tab_number.
    // * unit_tab_text
    //    * spacelive (localhost) is missing table: unit_tab_text.
    // * unit_tab_varchar
    //    * spacelive (localhost) is missing table: unit_tab_varchar.
    // * unit_text
    //    * spacelive (localhost) is missing table: unit_text.
    // * unit_varchar
    //    * spacelive (localhost) is missing table: unit_varchar.

    $sql_array[] = "SET NAMES utf8";
    $sql_array[] = "SET foreign_key_checks = 0";
    $sql_array[] = "SET time_zone = 'SYSTEM'";
    $sql_array[] = "SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'";

    $sql_array[] = "DROP TABLE IF EXISTS `approval_notice`";
    $sql_array[] =<<<SQL
CREATE TABLE `approval_notice` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `entity` varchar(20) NOT NULL,
  `oid` bigint(10) NOT NULL,
  `mail_to` text NOT NULL,
  `message` text NOT NULL,
  `mail_date` datetime NOT NULL,
  `update_by` bigint(10) NOT NULL,
  `status` varchar(20) NOT NULL,
  `remarks` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `entity_oid` (`entity`,`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `menu_hotspot`";
    $sql_array[] =<<<SQL
CREATE TABLE `menu_hotspot` (
  `uuid` bigint(20) unsigned NOT NULL,
  `oid` bigint(20) unsigned NOT NULL,
  `publish` datetime DEFAULT NULL,
  `unpublish` datetime DEFAULT NULL,
  `enabled` char(1) NOT NULL DEFAULT 'Y',
  `saved` char(1) NOT NULL DEFAULT 'N',
  `deleted` char(1) NOT NULL DEFAULT 'N',
  `last_modified` datetime NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `menu_hotspot_data`";
    $sql_array[] =<<<SQL
CREATE TABLE `menu_hotspot_data` (
  `uuid` bigint(20) unsigned NOT NULL,
  `is_enabled` varchar(255) DEFAULT NULL,
  `menu_name` varchar(255) DEFAULT NULL,
  `hotspot_type` varchar(255) DEFAULT NULL,
  `hotspot_entity` varchar(255) DEFAULT NULL,
  `hotspot_content` longtext,
  `hotspot_content_cht` longtext,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `menu_hotspot_data_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `menu_hotspot` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `menu_hotspot_datetime`";
    $sql_array[] =<<<SQL
CREATE TABLE `menu_hotspot_datetime` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` datetime NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `menu_hotspot_datetime_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `menu_hotspot` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `menu_hotspot_file`";
    $sql_array[] =<<<SQL
CREATE TABLE `menu_hotspot_file` (
  `uuid` bigint(20) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `mime` varchar(255) NOT NULL,
  `filesize` bigint(20) unsigned NOT NULL,
  `uri` varchar(255) NOT NULL,
  `alt` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`,`filename`),
  CONSTRAINT `menu_hotspot_file_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `menu_hotspot` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `menu_hotspot_number`";
    $sql_array[] =<<<SQL
CREATE TABLE `menu_hotspot_number` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` bigint(20) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `menu_hotspot_number_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `menu_hotspot` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `menu_hotspot_text`";
    $sql_array[] =<<<SQL
CREATE TABLE `menu_hotspot_text` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `menu_hotspot_text_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `menu_hotspot` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `menu_hotspot_varchar`";
    $sql_array[] =<<<SQL
CREATE TABLE `menu_hotspot_varchar` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `menu_hotspot_varchar_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `menu_hotspot` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `mobile_page`";
    $sql_array[] =<<<SQL
CREATE TABLE `mobile_page` (
  `uuid` bigint(20) unsigned NOT NULL,
  `oid` bigint(20) unsigned NOT NULL,
  `publish` datetime DEFAULT NULL,
  `unpublish` datetime DEFAULT NULL,
  `enabled` char(1) NOT NULL DEFAULT 'Y',
  `saved` char(1) NOT NULL DEFAULT 'N',
  `deleted` char(1) NOT NULL DEFAULT 'N',
  `last_modified` datetime NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `mobile_page_data`";
    $sql_array[] =<<<SQL
CREATE TABLE `mobile_page_data` (
  `uuid` bigint(20) unsigned NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `title_cht` varchar(255) DEFAULT NULL,
  `content` longtext,
  `content_cht` longtext,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `mobile_page_data_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `mobile_page` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `mobile_page_datetime`";
    $sql_array[] =<<<SQL
CREATE TABLE `mobile_page_datetime` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` datetime NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `mobile_page_datetime_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `mobile_page` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `mobile_page_file`";
    $sql_array[] =<<<SQL
CREATE TABLE `mobile_page_file` (
  `uuid` bigint(20) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `mime` varchar(255) NOT NULL,
  `filesize` bigint(20) unsigned NOT NULL,
  `uri` varchar(255) NOT NULL,
  `alt` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`,`filename`),
  CONSTRAINT `mobile_page_file_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `mobile_page` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `mobile_page_number`";
    $sql_array[] =<<<SQL
CREATE TABLE `mobile_page_number` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` bigint(20) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `mobile_page_number_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `mobile_page` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `mobile_page_text`";
    $sql_array[] =<<<SQL
CREATE TABLE `mobile_page_text` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `mobile_page_text_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `mobile_page` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `mobile_page_varchar`";
    $sql_array[] =<<<SQL
CREATE TABLE `mobile_page_varchar` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `mobile_page_varchar_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `mobile_page` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `notice`";
    $sql_array[] =<<<SQL
CREATE TABLE `notice` (
  `uuid` bigint(20) unsigned NOT NULL,
  `oid` bigint(20) unsigned NOT NULL,
  `publish` datetime DEFAULT NULL,
  `unpublish` datetime DEFAULT NULL,
  `enabled` char(1) NOT NULL DEFAULT 'Y',
  `saved` char(1) NOT NULL DEFAULT 'N',
  `deleted` char(1) NOT NULL DEFAULT 'N',
  `last_modified` datetime NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `notice_data`";
    $sql_array[] =<<<SQL
CREATE TABLE `notice_data` (
  `uuid` bigint(20) unsigned NOT NULL,
  `msg` varchar(255) DEFAULT NULL,
  `msg_cht` varchar(255) DEFAULT NULL,
  `publish_date` datetime DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `notice_data_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `notice` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `notice_datetime`";
    $sql_array[] =<<<SQL
CREATE TABLE `notice_datetime` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` datetime NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `notice_datetime_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `notice` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `notice_file`";
    $sql_array[] =<<<SQL
CREATE TABLE `notice_file` (
  `uuid` bigint(20) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `mime` varchar(255) NOT NULL,
  `filesize` bigint(20) unsigned NOT NULL,
  `uri` varchar(255) NOT NULL,
  `alt` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`,`filename`),
  CONSTRAINT `notice_file_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `notice` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `notice_number`";
    $sql_array[] =<<<SQL
CREATE TABLE `notice_number` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` bigint(20) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `notice_number_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `notice` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `notice_text`";
    $sql_array[] =<<<SQL
CREATE TABLE `notice_text` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `notice_text_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `notice` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `notice_varchar`";
    $sql_array[] =<<<SQL
CREATE TABLE `notice_varchar` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `notice_varchar_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `notice` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `share_related`";
    $sql_array[] =<<<SQL
CREATE TABLE `share_related` (
  `uuid` bigint(20) unsigned NOT NULL,
  `oid` bigint(20) unsigned NOT NULL,
  `publish` datetime DEFAULT NULL,
  `unpublish` datetime DEFAULT NULL,
  `enabled` char(1) NOT NULL DEFAULT 'Y',
  `saved` char(1) NOT NULL DEFAULT 'N',
  `deleted` char(1) NOT NULL DEFAULT 'N',
  `last_modified` datetime NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `share_related_data`";
    $sql_array[] =<<<SQL
CREATE TABLE `share_related_data` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `share_related_data_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `share_related` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `share_related_datetime`";
    $sql_array[] =<<<SQL
CREATE TABLE `share_related_datetime` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` datetime NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `share_related_datetime_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `share_related` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `share_related_file`";
    $sql_array[] =<<<SQL
CREATE TABLE `share_related_file` (
  `uuid` bigint(20) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `mime` varchar(255) NOT NULL,
  `filesize` bigint(20) unsigned NOT NULL,
  `uri` varchar(255) NOT NULL,
  `alt` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`,`filename`),
  CONSTRAINT `share_related_file_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `share_related` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `share_related_number`";
    $sql_array[] =<<<SQL
CREATE TABLE `share_related_number` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` bigint(20) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `share_related_number_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `share_related` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `share_related_text`";
    $sql_array[] =<<<SQL
CREATE TABLE `share_related_text` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `share_related_text_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `share_related` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `share_related_varchar`";
    $sql_array[] =<<<SQL
CREATE TABLE `share_related_varchar` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `share_related_varchar_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `share_related` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit` (
  `uuid` bigint(20) unsigned NOT NULL,
  `oid` bigint(20) unsigned NOT NULL,
  `publish` datetime DEFAULT NULL,
  `unpublish` datetime DEFAULT NULL,
  `enabled` char(1) NOT NULL DEFAULT 'Y',
  `saved` char(1) NOT NULL DEFAULT 'N',
  `deleted` char(1) NOT NULL DEFAULT 'N',
  `last_modified` datetime NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_data`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_data` (
  `uuid` bigint(20) unsigned NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `title_cht` varchar(255) DEFAULT NULL,
  `unit_type` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `telephone` varchar(255) DEFAULT NULL,
  `fax` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `unit_data_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_datetime`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_datetime` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` datetime NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_datetime_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_file`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_file` (
  `uuid` bigint(20) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `mime` varchar(255) NOT NULL,
  `filesize` bigint(20) unsigned NOT NULL,
  `uri` varchar(255) NOT NULL,
  `alt` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`,`filename`),
  CONSTRAINT `unit_file_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_number`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_number` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` bigint(20) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_number_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_tab`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_tab` (
  `uuid` bigint(20) unsigned NOT NULL,
  `oid` bigint(20) unsigned NOT NULL,
  `publish` datetime DEFAULT NULL,
  `unpublish` datetime DEFAULT NULL,
  `enabled` char(1) NOT NULL DEFAULT 'Y',
  `saved` char(1) NOT NULL DEFAULT 'N',
  `deleted` char(1) NOT NULL DEFAULT 'N',
  `last_modified` datetime NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_tab_data`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_tab_data` (
  `uuid` bigint(20) unsigned NOT NULL,
  `is_enable` varchar(255) DEFAULT NULL,
  `is_show_right_column` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `content` longtext,
  `content_cht` longtext,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `unit_tab_data_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit_tab` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_tab_datetime`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_tab_datetime` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` datetime NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_tab_datetime_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit_tab` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_tab_file`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_tab_file` (
  `uuid` bigint(20) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `mime` varchar(255) NOT NULL,
  `filesize` bigint(20) unsigned NOT NULL,
  `uri` varchar(255) NOT NULL,
  `alt` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`,`filename`),
  CONSTRAINT `unit_tab_file_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit_tab` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_tab_number`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_tab_number` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` bigint(20) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_tab_number_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit_tab` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_tab_text`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_tab_text` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_tab_text_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit_tab` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_tab_varchar`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_tab_varchar` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_tab_varchar_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit_tab` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_text`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_text` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_text_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    $sql_array[] = "DROP TABLE IF EXISTS `unit_varchar`";
    $sql_array[] =<<<SQL
CREATE TABLE `unit_varchar` (
  `uuid` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `seq` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uuid`,`name`,`seq`),
  CONSTRAINT `unit_varchar_fk_uuid` FOREIGN KEY (`uuid`) REFERENCES `unit` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL;

    // Map internal subject group to friendly subject group
    // +-----+---------------------------------------+
    // | oid | internal Subject                      | Friendly Subject
    // +-----+---------------------------------------+
    // | 461 | Accounting                            | 69
    // | 462 | Business Logistics                    | 102
    // | 463 | Business Management                   | 101
    // | 464 | Business Soft Skills                  | 98
    // | 465 | Corporate Governance                  | 99
    // | 467 | Finance                               | 84
    // | 468 | Hospitality & Tourism                 | 109
    // | 469 | Marketing                             | 141
    // | 470 | Architectural Studies                 | 86
    // | 471 | Art and Design                        | 92
    // | 479 | Education                             | 145
    // | 481 | Media and Communication               | 140
    // | 491 | Nursing Studies and Health Care       | 137
    // | 495 | Information Management and Technology | 112
    // | 496 | Library and Information Science       | 114
    // | 498 | Chinese Medicine Pharmaceutics        | 106
    // | 499 | Acupuncture and Tui-Na                | 103
    // +-----+---------------------------------------+

    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:69:',  1  from share_data where int_subject=':int_subject:461:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:102:', 2  from share_data where int_subject=':int_subject:462:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:101:', 3  from share_data where int_subject=':int_subject:463:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:98:',  4  from share_data where int_subject=':int_subject:464:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:99:',  5  from share_data where int_subject=':int_subject:465:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:84:',  6  from share_data where int_subject=':int_subject:467:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:109:', 7  from share_data where int_subject=':int_subject:468:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:141:', 8  from share_data where int_subject=':int_subject:469:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:86:',  9  from share_data where int_subject=':int_subject:470:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:92:',  10 from share_data where int_subject=':int_subject:471:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:145:', 11 from share_data where int_subject=':int_subject:479:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:140:', 12 from share_data where int_subject=':int_subject:481:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:137:', 13 from share_data where int_subject=':int_subject:491:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:112:', 14 from share_data where int_subject=':int_subject:495:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:114:', 15 from share_data where int_subject=':int_subject:496:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:106:', 16 from share_data where int_subject=':int_subject:498:'";
    $sql_array[] = "INSERT INTO share_varchar select uuid, 'subjects', ':subject:103:', 17 from share_data where int_subject=':int_subject:499:'";


    $sql_array[] = "insert into frontpage_datetime select uuid, 'quick_link.publish_date', '2012-08-01 00:00:00', seq from frontpage_varchar where name='quick_link.url'";
    $sql_array[] = "insert into frontpage_datetime select uuid, 'quick_link.expiry_date', '0000-00-00 00:00:00', seq from frontpage_varchar where name='quick_link.url'";


    // Add Sharing for College
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:32:', 0  from  share_data where college = ':unit:32:'";       // CBF
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:58:', 1  from  share_data where college = ':unit:58:'";       // CLST
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:59:', 2  from  share_data where college = ':unit:59:'";       // CHL
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:1330:', 3  from  share_data where centre = ':unit:1330:'";    // CCPA
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:1331:', 4  from  share_data where centre = ':unit:1331:'";    // CEC
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:1335:', 5  from  share_data where centre = ':unit:1335:'";    // CLS
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:1340:', 6  from  share_data where centre = ':unit:1340:'";    // CLT
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:13431:', 7  from  share_data where centre = ':unit:13431:'";  // CIDP
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:13432:', 8  from  share_data where centre = ':unit:13432:'";  // CCL
    $sql_array[] = "insert into share_varchar select uuid, 'unit', ':unit:13433:', 9  from  share_data where centre = ':unit:13433:'";  // CELL

    // Staff Profile
    $sql_array[] = "update staff_varchar set value=replace(value, ':centre:', ':unit:' ) where name = 'centre'";
    $sql_array[] = "update staff_varchar set value=replace(value, ':college:', ':unit:' ) where name = 'college'";
    $sql_array[] = "update staff_varchar set name='unit' where name in ('college')";
    $sql_array[] = "update staff_varchar set name='unit', seq=seq+10 where name in ('centre')";

    // Event
    $sql_array[] = "update event_varchar set value=replace(value, ':centre:', ':unit:' ) where name = 'centre'";
    $sql_array[] = "update event_varchar set value=replace(value, ':college:', ':unit:' ) where name = 'college'";
    $sql_array[] = "update event_varchar set name='unit' where name in ('college')";
    $sql_array[] = "update event_varchar set name='unit', seq=seq+10 where name in ('centre')";

    // Event Series
    $sql_array[] = "update series_varchar set value=replace(value, ':centre:', ':unit:' ) where name = 'centre'";
    $sql_array[] = "update series_varchar set value=replace(value, ':college:', ':unit:' ) where name = 'college'";
    $sql_array[] = "update series_varchar set name='unit' where name in ('college')";
    $sql_array[] = "update series_varchar set name='unit', seq=seq+10 where name in ('centre')";

    // Update Menu ************* IMPORTANT
    $sql_array[] = "delete from menu where publish is null and saved='N'";

    // delete alias data to avoid redirection
    $sql_array[] = "delete from alias where oid in (36261, 36237, 36224, 36262, 36236, 36264, 36235)";
    $sql_array[] = "delete from cms_global_url where alias_oid in (36261, 36237, 36224, 36262, 36236, 36264, 36235)";
    $sql_array[] = "delete from global_url where alias_oid in (36261, 36237, 36224, 36262, 36236, 36264, 36235)";

    return $sql_array;
}

/**
 * Logging class:
 * - contains lfile, lwrite and lclose public methods
 * - lfile sets path and name of log file
 * - lwrite writes message to the log file (and implicitly opens log file)
 * - lclose closes log file
 * - first call of lwrite method will open log file implicitly
 * - message is written with the following format: [d/M/Y:H:i:s] (script name) message
 */
class Logging
{
    // declare log file and file pointer as private properties
    private $log_file, $fp;
    // set log file (path and name)
    public function lfile($path) {
        $this->log_file = $path;
    }
    // write message to the log file
    public function lwrite($message) {
        // if file pointer doesn't exist, then open log file
        if (!is_resource($this->fp)) {
            $this->lopen();
        }
        // define script name
        $script_name = pathinfo($_SERVER['PHP_SELF'], PATHINFO_FILENAME);
        // define current time and suppress E_WARNING if using the system TZ settings
        // (don't forget to set the INI setting date.timezone)
        $time = @date('[Y/m/d H:i:s]');
        // write current time, script name and message to the log file
        // fwrite($this->fp, "$time ($script_name) $message" . PHP_EOL);
        fwrite($this->fp, "$message");
    }
    // close log file (it's always a good idea to close a file when you're done with it)
    public function lclose() {
        fclose($this->fp);
    }
    // open log file (private method)
    private function lopen() {
        // in case of Windows set default log file
        if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
            $log_file_default = 'c:/php/logfile.txt';
        }
        // set default log file for Linux and other systems
        else {
            $log_file_default = '/tmp/logfile.txt';
        }
        // define log file from lfile method or use previously set default
        $lfile = $this->log_file ? $this->log_file : $log_file_default;
        // open log file for writing only and place file pointer at the end of the file
        // (if the file does not exist, try to create it)
        $this->fp = fopen($lfile, 'a') or exit("Can't open $lfile!");
    }
}

// End of File
